package database

import (
	"fmt"
	"sugar/utils"

	//	"strconv"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	uuid "github.com/satori/go.uuid"
	"gopkg.in/guregu/null.v3"
)

type DatabaseOp struct {
	tx *sqlx.Tx
}

var db *sqlx.DB
var connectErr error

func init() {
	host := utils.GetEnvDefault("DB_HOST", "192.168.100.103")
	port := utils.GetEnvDefault("DB_PORT", "10202")
	dbName := utils.GetEnvDefault("DB_NAME", "sugar2")
	password := utils.GetEnvDefault("DB_PASSWORD", "postgres")
	user := utils.GetEnvDefault("DB_USER", "postgres")
	dbtype := utils.GetEnvDefault("DB_TYPE", "postgresql")
	var strConnect string
	switch dbtype {
	case "postgresql":
		strConnect := fmt.Sprint(dbtype, "://", user, ":", password, "@", host, ":", port, "/", dbName, "?sslmode=disable")
		db, connectErr = sqlx.Connect("postgres", strConnect)
	case "mysql":
		strConnect := fmt.Sprint(dbtype, "://", user, ":", password, "@tcp(", host, ":", port, ")/", dbName)
		db, connectErr = sqlx.Connect("mysql", strConnect)
	}

	fmt.Print(strConnect)
	if connectErr != nil {
		panic(connectErr)
	}
	//var err error

}

func NewDatabaseOp() *DatabaseOp {
	dbOp := DatabaseOp{}
	return &dbOp
}

func (dbOp *DatabaseOp) Begin() error {
	if connectErr != nil {
		return connectErr
	}
	var err error
	dbOp.tx, err = db.Beginx()
	return err
}

func (dbOp *DatabaseOp) Rollback() error {
	if dbOp.tx != nil {
		return dbOp.tx.Rollback()
	}
	return nil
}

func (dbOp *DatabaseOp) Commit() error {
	if dbOp.tx != nil {
		return dbOp.tx.Commit()
	}
	return nil
}

func (dbOp *DatabaseOp) GetUserByEmail(email string) (User, error) {
	sqlresult := dbOp.tx.QueryRowx("SELECT * FROM user_platform WHERE email=$1", email)
	var u User
	err := sqlresult.StructScan(&u)
	return u, err
}

func (dbOp *DatabaseOp) GetUserByPhone(phone string) (User, error) {
	sqlresult := dbOp.tx.QueryRowx("SELECT * FROM user_platform WHERE phone=$1", phone)
	var u User
	err := sqlresult.StructScan(&u)
	return u, err
}

func (dbOp *DatabaseOp) GetUserByUserName(userName string) (User, error) {
	sqlresult := dbOp.tx.QueryRowx("SELECT * FROM user_platform WHERE user_name=$1", userName)
	var u User
	err := sqlresult.StructScan(&u)
	return u, err
}

func (dbOp *DatabaseOp) GetUserByUserID(userID string) (User, error) {
	sqlresult := dbOp.tx.QueryRowx("SELECT * FROM user_platform WHERE id=$1", userID)
	var u User
	err := sqlresult.StructScan(&u)
	return u, err
}

func (dbOp *DatabaseOp) CreateUserByEmail(email string, passwordHash string, userType int) (string, error) {
	userID := uuid.NewV4()
	_, err := dbOp.tx.Exec("INSERT INTO user_platform (id, email, password_hash, user_type, banned, create_date, operate_date, operator) values ($1,$2,$3,$4,0,now(),now(),$5)", userID, email, passwordHash, userType, userID)
	if err != nil {
		return "", err
	}
	return userID.String(), nil
}

func (dbOp *DatabaseOp) ResetPasswordByEmail(email string, passwordHash string) error {
	_, err := dbOp.tx.Exec("UPDATE user_platform SET password_hash=$1, operate_date=now() WHERE email=$2", passwordHash, email)
	return err
}

func (dbOp *DatabaseOp) ResetPasswordByID(userID string, passwordHash string) error {
	_, err := dbOp.tx.Exec("UPDATE user_platform SET password_hash=$1, operator=$2, operate_date=now() WHERE id=$3", passwordHash, userID, userID)
	return err
}

func (dbOp *DatabaseOp) ResetEmailByID(userID string, email string) error {
	_, err := dbOp.tx.Exec("UPDATE user_platform SET email=$1, operator=$2, operate_date=now() WHERE id=$3", email, userID, userID)
	return err
}

func (dbOp *DatabaseOp) UpdateUserInfo(userID string, sex null.Int, age null.Int, userName null.String, name null.String, info null.String) error {
	sqlstr := "UPDATE user_platform SET "
	count := 0
	values := make([]interface{}, 0)
	if sex.Valid {
		count++
		sqlstr += fmt.Sprintf("sex=$%d, ", count)
		values = append(values, sex)
	}
	if age.Valid {
		count++
		sqlstr += fmt.Sprintf("age=$%d, ", count)
		values = append(values, age)
	}
	if userName.Valid {
		count++
		sqlstr += fmt.Sprintf("user_name=$%d, ", count)
		values = append(values, userName)
	}
	if name.Valid {
		count++
		sqlstr += fmt.Sprintf("name=$%d, ", count)
		values = append(values, name)
	}
	if info.Valid {
		count++
		sqlstr += fmt.Sprintf("info=$%d, ", count)
		values = append(values, info)
	}
	sqlstr += fmt.Sprintf("operator=$%d WHERE id=$%d", count+1, count+2)
	values = append(values, userID)
	values = append(values, userID)
	_, err := dbOp.tx.Exec(sqlstr, values...)
	return err
}

//
func (dbOp *DatabaseOp) IsModelExist(userID string, modelName string) (bool, error) {
	sqlresult := dbOp.tx.QueryRow("SELECT count(id) FROM model WHERE user_id=$1 AND name=$2", userID, modelName)
	var count int
	err := sqlresult.Scan(&count)
	return count != 0, err
}

//
func (dbOp *DatabaseOp) ListModelByUser(userID string, pageSize int, pageNo int) ([]Model, error) {
	res := make([]Model, 0)
	sqlrows, err := dbOp.tx.Queryx("SELECT id, name, model_class, dependencies, description, create_date, type, model_file_name, public  FROM model WHERE user_id=$1 LIMIT $2 OFFSET $3", userID, pageSize, pageSize*pageNo)
	if err != nil {
		return res, err
	}
	for sqlrows.Next() {
		var m Model
		sqlrows.StructScan(&m)
		res = append(res, m)
	}
	return res, err
}

//
func (dbOp *DatabaseOp) ListDatasetByUser(userID string, pageSize int, pageNo int) ([]Dataset, error) {
	res := make([]Dataset, 0)
	sqlrows, err := dbOp.tx.Queryx("SELECT * FROM dataset WHERE user_id=$1 LIMIT $2 OFFSET $3", userID, pageSize, pageSize*pageNo)
	if err != nil {
		return res, err
	}
	for sqlrows.Next() {
		var m Dataset
		sqlrows.StructScan(&m)
		res = append(res, m)
	}
	return res, err
}

//
func (dbOp *DatabaseOp) GetModelByUserAndName(userID string, modelName string) (Model, error) {
	sqlresult := dbOp.tx.QueryRowx("SELECT * FROM model WHERE user_id=$1 AND name=$2", userID, modelName)
	var m Model
	err := sqlresult.StructScan(&m)
	return m, err
}

//
func (dbOp *DatabaseOp) IsDatasetExist(userID string, datasetName string) (bool, error) {
	sqlresult := dbOp.tx.QueryRow("SELECT count(id) FROM dataset WHERE user_id=$1 AND name=$2", userID, datasetName)
	var count int
	err := sqlresult.Scan(&count)
	return count != 0, err
}

func (dbOp *DatabaseOp) ListTrainjobByUser(userID string) ([]Trainjob, error) {
	res := make([]Trainjob, 0)
	sqlrows, err := dbOp.tx.Queryx("SELECT * FROM train_job WHERE user_id=$1 AND banned != 1", userID)
	for sqlrows.Next() {
		var m Trainjob
		sqlrows.StructScan(&m)
		res = append(res, m)
	}
	return res, err
}

func (dbOp *DatabaseOp) ListAppByUser(userID string) ([]App, error) {
	res := make([]App, 0)
	sqlrows, err := dbOp.tx.Queryx("SELECT * FROM application WHERE user_id=$1", userID)
	for sqlrows.Next() {
		var m App
		sqlrows.StructScan(&m)
		res = append(res, m)
	}
	return res, err
}

//
func (dbOp *DatabaseOp) GetModelByID(modelID int64) (*Model, error) {
	sqlrow := dbOp.tx.QueryRowx("SELECT * FROM model WHERE id=$1", modelID)
	var m Model
	err := sqlrow.StructScan(&m)
	return &m, err
}

//
func (dbOp *DatabaseOp) GetTrainjobByID(trainjobID int64) (*Trainjob, error) {
	sqlrow := dbOp.tx.QueryRowx("SELECT * FROM train_job WHERE id=$1", trainjobID)
	var m Trainjob
	err := sqlrow.StructScan(&m)
	return &m, err
}

//
func (dbOp *DatabaseOp) GetAppByID(appID int64) (*App, error) {
	sqlrow := dbOp.tx.QueryRowx("SELECT * FROM application WHERE id=$1", appID)
	var m App
	err := sqlrow.StructScan(&m)
	return &m, err
}

//
func (dbOp *DatabaseOp) GetDatasetByID(datasetID int64) (*Dataset, error) {
	sqlrow := dbOp.tx.QueryRowx("SELECT * FROM dataset WHERE id=$1", datasetID)
	var m Dataset
	err := sqlrow.StructScan(&m)
	return &m, err
}

//
func (dbOp *DatabaseOp) CreateDataset(userID string, datasetName string, datasetType int, datasetObject string, datasetObjectType int, token null.String, description null.String, public int) (int64, error) {
	// dataset_id := uuid.NewV4().String()
	var id int64
	err := dbOp.tx.QueryRow("INSERT INTO dataset (user_id, name, type, object, object_type, token, description, create_date, operate_date, operator, banned, public) VALUES ($1,$2,$3,$4,$5,$6,$7,now(),now(),$8,0,$9) RETURNING ID", userID, datasetName, datasetType, datasetObject, datasetObjectType, token, description, userID, public).Scan(&id)
	if err != nil {
		// sqtx.Rollback()
		return -1, err
	}
	return id, err
}

//
func (dbOp *DatabaseOp) CreateModel(userID string, modelName string, modelClass string, modelData []byte, dependencies null.String, description null.String, modelType int, modelFileType int, modelFileName null.String, public int, curCommitID null.String) (int64, error) {
	var id int64
	err := dbOp.tx.QueryRow("INSERT INTO model (user_id, name, model_class, model_file_bytes, dependencies, description, create_date, banned, type, model_file_type, model_file_name, public, cur_commit_id, operator, operate_date) VALUES ($1,$2,$3,$4,$5,$6,now(),0,$7,$8,$9,$10,$11,$12,now()) RETURNING ID",
		userID, modelName, modelClass, modelData, dependencies, description, modelType, modelFileType, modelFileName, public, curCommitID, userID).Scan(&id)
	if err != nil {
		return -1, err
	}
	return id, err
}

//
func (dbOp *DatabaseOp) CreateTrainJob(userID string, trainJobName string, version int, projectID null.Int, description null.String, budget string, trainDatasetID int64, valDatasetID int64, modelID int64, modelCommitID null.String,
	modelArgs null.String, trainArgs null.String, paramSaveObject string, useAutoml int, trainJobType int, destScore string, maxTrials int) (int64, error) {
	var id int64
	err := dbOp.tx.QueryRow("INSERT INTO trainjob (user_id, name, version, project_id, description, budget, train_dataset_id, val_dataset_id, model_id, model_commit_id, model_args, train_args, status, param_save_object, datetime_started, banned, use_automl, type, dest_score, max_trials) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,0,$13,now(),0,$14,$15,$16,$17) RETURNING ID",
		userID, trainJobName, version, projectID, description, budget, trainDatasetID, valDatasetID, modelID, modelCommitID, modelArgs, trainArgs, paramSaveObject, useAutoml, trainJobType, destScore, maxTrials).Scan(&id)
	if err != nil {
		return -1, err
	}
	return id, err
}

//
func (dbOp *DatabaseOp) UpdateTrainJobStatus(trainJobID int64, status int) error {
	_, err := dbOp.tx.Exec("UPDATE trainjob SET status=$1 WHERE id=$2", status, trainJobID)
	return err
}

//
func (dbOp *DatabaseOp) CreateApp(userID string, appName string, trainJobID int64, projectID null.Int, modelID int64, modelCommitID null.String, description null.String, budget string, port int, replicasMin int, replicasMax int, appType int) (int64, error) {
	var id int64
	err := dbOp.tx.QueryRow("INSERT INTO app (user_id, name, train_job_id, project_id, model_id, model_commit_id, description, budget, port, replicas_min, replicas_max, status, datetime_started, banned, type) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11'INIT',now(),0,$12)",
		userID, appName, trainJobID, projectID, modelID, modelCommitID, description, budget, port, replicasMin, replicasMax, appType).Scan(&id)
	if err != nil {
		return -1, err
	}
	return id, err
}

//
func (dbOp *DatabaseOp) UpdateAppStatus(appID int64, status int) error {
	_, err := dbOp.tx.Exec("UPDATE app SET status=$1 WHERE id=$2", status, appID)
	return err
}

//
func (dbOp *DatabaseOp) IsAppExist(userID string, appName string) (bool, error) {
	sqlresult := dbOp.tx.QueryRow("SELECT count(id) FROM app WHERE user_id=$1 AND name=$2", userID, appName)
	var count int
	err := sqlresult.Scan(&count)
	return count != 0, err
}

//
func (dbOp *DatabaseOp) IsTrainJobExist(userID string, trainJobName string) (bool, error) {
	sqlresult := dbOp.tx.QueryRow("SELECT count(id) FROM trainjob WHERE user_id=$1 AND name=$2", userID, trainJobName)
	var count int
	err := sqlresult.Scan(&count)
	return count != 0, err
}
